﻿ 
	CREATE TABLE [CurrencyList]
	(
		[Id] [int] IDENTITY(1,1) NOT NULL,
		[Code] [nvarchar](100)  NOT NULL,
		[Name] [nvarchar](MAX),
		[Description] [nvarchar](MAX),		

		CONSTRAINT [PK_CurrencyListId] PRIMARY KEY CLUSTERED ([Id] ASC),
	)
	GO
	
	ALTER TABLE [CurrencyList] ADD  CONSTRAINT [AK_CurrencyList_Code] UNIQUE NONCLUSTERED 
	(
		[Code] ASC
	)
	GO
	
	INSERT INTO [CurrencyList] ([Code], [Name], [Description]) VALUES ('EUR', 'Value1', NULL)
	INSERT INTO [CurrencyList] ([Code], [Name], [Description]) VALUES ('HUF', 'Value2', NULL)
	INSERT INTO [CurrencyList] ([Code], [Name], [Description]) VALUES ('USD', 'Value3', NULL)
	GO

 
	CREATE TABLE [CustomerClassification]
	(
		[Id] [int] IDENTITY(1,1) NOT NULL,
		[Code] [nvarchar](100)  NOT NULL,
		[Name] [nvarchar](MAX),
		[Description] [nvarchar](MAX),		

		CONSTRAINT [PK_CustomerClassificationId] PRIMARY KEY CLUSTERED ([Id] ASC),
	)
	GO
	
	ALTER TABLE [CustomerClassification] ADD  CONSTRAINT [AK_CustomerClassification_Code] UNIQUE NONCLUSTERED 
	(
		[Code] ASC
	)
	GO
	
	INSERT INTO [CustomerClassification] ([Code], [Name], [Description]) VALUES ('VIP', 'Value1', 'Needs special care, important for the business')
	INSERT INTO [CustomerClassification] ([Code], [Name], [Description]) VALUES ('Normal', 'Value2', 'Normal treatment')
	INSERT INTO [CustomerClassification] ([Code], [Name], [Description]) VALUES ('Problematic', 'Value3', 'Suspicious customer')
	GO

 
	CREATE TABLE [Customer]
	(
		[Id] [int] IDENTITY(1,1) NOT NULL,
		[Name] [nvarchar](MAX) NOT NULL,
		
		[CustomerClassificationId] int ,
		
		CONSTRAINT [PK_CustomerId] PRIMARY KEY CLUSTERED ([Id] ASC),
	)
	GO
 

 
	CREATE TABLE [Person]
	(
		[Id] [int] IDENTITY(1,1) NOT NULL,
		[BirthPlace] [nvarchar](MAX) ,
		[BirthDate] [datetime] ,
		[Age] [int] ,
		
		[PersonNameFirstName] [nvarchar](MAX) NOT NULL,
		[PersonNameMiddleName] [nvarchar](MAX) ,
		[PersonNameLastName] [nvarchar](MAX) NOT NULL,
		
		CONSTRAINT [PK_PersonId] PRIMARY KEY CLUSTERED ([Id] ASC),
	)
	GO
	EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Birth place' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Person', @level2type=N'COLUMN',@level2name=N'BirthPlace'
	GO
	EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Birth date' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Person', @level2type=N'COLUMN',@level2name=N'BirthDate'
	GO
	EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Age of the customer in years' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Person', @level2type=N'COLUMN',@level2name=N'Age'
	GO
 

 
	CREATE TABLE [Company]
	(
		[Id] [int] IDENTITY(1,1) NOT NULL,
		[TaxNumber] [nvarchar](MAX) ,
		
		[CompanyNameLongName] [nvarchar](MAX) ,
		[CompanyNameShortName] [nvarchar](MAX) ,
		
		CONSTRAINT [PK_CompanyId] PRIMARY KEY CLUSTERED ([Id] ASC),
	)
	GO
 

 
	CREATE TABLE [BusinessEntityType]
	(
		[Id] [int] IDENTITY(1,1) NOT NULL,
		[Code] [nvarchar](100)  NOT NULL,
		[Name] [nvarchar](MAX),
		[Description] [nvarchar](MAX),		

		CONSTRAINT [PK_BusinessEntityTypeId] PRIMARY KEY CLUSTERED ([Id] ASC),
	)
	GO
	
	ALTER TABLE [BusinessEntityType] ADD  CONSTRAINT [AK_BusinessEntityType_Code] UNIQUE NONCLUSTERED 
	(
		[Code] ASC
	)
	GO
	
	INSERT INTO [BusinessEntityType] ([Code], [Name], [Description]) VALUES ('Corp', 'Corporation', NULL)
	INSERT INTO [BusinessEntityType] ([Code], [Name], [Description]) VALUES ('Inc', 'Incorporation', NULL)
	INSERT INTO [BusinessEntityType] ([Code], [Name], [Description]) VALUES ('Ltd', 'Limited', NULL)
	GO

 
	CREATE TABLE [PersonNameTitle]
	(
		[Id] [int] IDENTITY(1,1) NOT NULL,
		[Code] [nvarchar](100)  NOT NULL,
		[Name] [nvarchar](MAX),
		[Description] [nvarchar](MAX),		

		CONSTRAINT [PK_PersonNameTitleId] PRIMARY KEY CLUSTERED ([Id] ASC),
	)
	GO
	
	ALTER TABLE [PersonNameTitle] ADD  CONSTRAINT [AK_PersonNameTitle_Code] UNIQUE NONCLUSTERED 
	(
		[Code] ASC
	)
	GO
	
	INSERT INTO [PersonNameTitle] ([Code], [Name], [Description]) VALUES ('dr', 'Doctor', NULL)
	INSERT INTO [PersonNameTitle] ([Code], [Name], [Description]) VALUES ('prof', 'Professor', NULL)
	GO

 
	CREATE TABLE [Order]
	(
		[Id] [int] IDENTITY(1,1) NOT NULL,
		[OrderDate] [datetime] NOT NULL,
		[OrderDueDate] [datetime] NOT NULL,
		
		[ShipmentTypeId] int ,
		[CustomerId] int NOT NULL,
		
		CONSTRAINT [PK_OrderId] PRIMARY KEY CLUSTERED ([Id] ASC),
	)
	GO
 

 
	CREATE TABLE [ShipmentType]
	(
		[Id] [int] IDENTITY(1,1) NOT NULL,
		[Code] [nvarchar](100)  NOT NULL,
		[Name] [nvarchar](MAX),
		[Description] [nvarchar](MAX),		

		CONSTRAINT [PK_ShipmentTypeId] PRIMARY KEY CLUSTERED ([Id] ASC),
	)
	GO
	
	ALTER TABLE [ShipmentType] ADD  CONSTRAINT [AK_ShipmentType_Code] UNIQUE NONCLUSTERED 
	(
		[Code] ASC
	)
	GO
	
	INSERT INTO [ShipmentType] ([Code], [Name], [Description]) VALUES ('PickUp', 'PickUp', NULL)
	INSERT INTO [ShipmentType] ([Code], [Name], [Description]) VALUES ('Post', 'Post', NULL)
	GO

 
	CREATE TABLE [OrderItem]
	(
		[Id] [int] IDENTITY(1,1) NOT NULL,
		[Unit] [int] ,
		
		[OrderId] int NOT NULL,
		[ProductId] int NOT NULL,
		
		CONSTRAINT [PK_OrderItemId] PRIMARY KEY CLUSTERED ([Id] ASC),
	)
	GO
 

 
	CREATE TABLE [Product]
	(
		[Id] [int] IDENTITY(1,1) NOT NULL,
		[Name] [nvarchar](MAX) ,
		[BasePrice] [nvarchar](MAX) ,
		[DiscountedPrice] [nvarchar](MAX) ,
		
		[ProductGroupId] int NOT NULL,
		
		CONSTRAINT [PK_ProductId] PRIMARY KEY CLUSTERED ([Id] ASC),
	)
	GO
 

 
	CREATE TABLE [ProductGroup]
	(
		[Id] [int] IDENTITY(1,1) NOT NULL,
		[Name] [nvarchar](MAX) ,
		
		
		CONSTRAINT [PK_ProductGroupId] PRIMARY KEY CLUSTERED ([Id] ASC),
	)
	GO
 

 
	CREATE TABLE [SalesAction]
	(
		[Id] [int] IDENTITY(1,1) NOT NULL,
		
		
		CONSTRAINT [PK_SalesActionId] PRIMARY KEY CLUSTERED ([Id] ASC),
	)
	GO
 

 
	CREATE TABLE [DiscountGroup]
	(
		[Id] [int] IDENTITY(1,1) NOT NULL,
		
		[SalesActionId] int NOT NULL,
		[NameCode] [nvarchar](MAX) ,
		[NameName] [nvarchar](MAX) ,
		[NameDescription] [nvarchar](MAX) ,
		
		CONSTRAINT [PK_DiscountGroupId] PRIMARY KEY CLUSTERED ([Id] ASC),
	)
	GO
 

 
------------------------------One to Many Associations, foreign keys--------------
		
 
		
 
	ALTER TABLE [Customer]  
	WITH CHECK ADD  CONSTRAINT [FK_Customer_CustomerClassificationId] FOREIGN KEY([CustomerClassificationId])
	REFERENCES [dbo].[CustomerClassification] ([Id])
	GO
		
 
		
 
		
 
		
 
		
 
	ALTER TABLE [Order]  
	WITH CHECK ADD  CONSTRAINT [FK_Order_ShipmentTypeId] FOREIGN KEY([ShipmentTypeId])
	REFERENCES [dbo].[ShipmentType] ([Id])
	GO
	ALTER TABLE [Order]  
	WITH CHECK ADD  CONSTRAINT [FK_Order_CustomerId] FOREIGN KEY([CustomerId])
	REFERENCES [dbo].[Customer] ([Id])
	GO
		
 
		
 
	ALTER TABLE [OrderItem]  
	WITH CHECK ADD  CONSTRAINT [FK_OrderItem_OrderId] FOREIGN KEY([OrderId])
	REFERENCES [dbo].[Order] ([Id])
	GO
	ALTER TABLE [OrderItem]  
	WITH CHECK ADD  CONSTRAINT [FK_OrderItem_ProductId] FOREIGN KEY([ProductId])
	REFERENCES [dbo].[Product] ([Id])
	GO
		
 
	ALTER TABLE [Product]  
	WITH CHECK ADD  CONSTRAINT [FK_Product_ProductGroupId] FOREIGN KEY([ProductGroupId])
	REFERENCES [dbo].[ProductGroup] ([Id])
	GO
		
 
		
 
		
 
	ALTER TABLE [DiscountGroup]  
	WITH CHECK ADD  CONSTRAINT [FK_DiscountGroup_SalesActionId] FOREIGN KEY([SalesActionId])
	REFERENCES [dbo].[SalesAction] ([Id])
	GO
		
 
------------------------------Many to Many Associations----------------------------
	CREATE TABLE [Assoc_CustomerClassification_SalesAction]
	(
		[CustomerClassificationId] [int] NOT NULL,
		[SalesActionId] [int] NOT NULL,
		CONSTRAINT [PK_Assoc_CustomerClassification_SalesAction] PRIMARY KEY CLUSTERED 
			([CustomerClassificationId] ASC,
			[SalesActionId] ASC)
	)
	GO
	ALTER TABLE [Assoc_CustomerClassification_SalesAction]  
	WITH CHECK ADD  CONSTRAINT [FK1_Assoc_CustomerClassification_SalesAction] FOREIGN KEY([CustomerClassificationId])
	REFERENCES [dbo].[CustomerClassification] ([Id])
	GO

	ALTER TABLE [Assoc_CustomerClassification_SalesAction]  
	WITH CHECK ADD  CONSTRAINT [FK2_Assoc_CustomerClassification_SalesAction] FOREIGN KEY([SalesActionId])
	REFERENCES [dbo].[SalesAction] ([Id])
	GO
		
 
	CREATE TABLE [Assoc_Product_DiscountGroup]
	(
		[ProductId] [int] NOT NULL,
		[DiscountGroupId] [int] NOT NULL,
		CONSTRAINT [PK_Assoc_Product_DiscountGroup] PRIMARY KEY CLUSTERED 
			([ProductId] ASC,
			[DiscountGroupId] ASC)
	)
	GO
	ALTER TABLE [Assoc_Product_DiscountGroup]  
	WITH CHECK ADD  CONSTRAINT [FK1_Assoc_Product_DiscountGroup] FOREIGN KEY([ProductId])
	REFERENCES [dbo].[Product] ([Id])
	GO

	ALTER TABLE [Assoc_Product_DiscountGroup]  
	WITH CHECK ADD  CONSTRAINT [FK2_Assoc_Product_DiscountGroup] FOREIGN KEY([DiscountGroupId])
	REFERENCES [dbo].[DiscountGroup] ([Id])
	GO
		
 
	CREATE TABLE [Assoc_ProductGroup_DiscountGroup]
	(
		[ProductGroupId] [int] NOT NULL,
		[DiscountGroupId] [int] NOT NULL,
		CONSTRAINT [PK_Assoc_ProductGroup_DiscountGroup] PRIMARY KEY CLUSTERED 
			([ProductGroupId] ASC,
			[DiscountGroupId] ASC)
	)
	GO
	ALTER TABLE [Assoc_ProductGroup_DiscountGroup]  
	WITH CHECK ADD  CONSTRAINT [FK1_Assoc_ProductGroup_DiscountGroup] FOREIGN KEY([ProductGroupId])
	REFERENCES [dbo].[ProductGroup] ([Id])
	GO

	ALTER TABLE [Assoc_ProductGroup_DiscountGroup]  
	WITH CHECK ADD  CONSTRAINT [FK2_Assoc_ProductGroup_DiscountGroup] FOREIGN KEY([DiscountGroupId])
	REFERENCES [dbo].[DiscountGroup] ([Id])
	GO
		
 

 
